﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using Microsoft.ApplicationBlocks.Data;
using KP_CommonLibrary.KP_ObjectReferences;
namespace KP_CommonLibrary.KP_DataAccess
{
    /// <summary>
    /// DataAccess Base dành cho phiếu
    /// <para>
    /// Chỉ thực hiện base các phương thức insert,update,delete
    /// 
    /// </para>
    /// <para>Phương thức Select không base vì nó là đặc thù : Số bản ghi lấy lần đầu, lọc,...</para>
    /// </summary>
    public class KP_DALVoucher2 : KP_DALBase
    {
        /// <summary>
        /// Tên bảng chính
        /// </summary>
        public string MasterTable { get; set; }
        /// <summary>
        /// Tên bảng chi tiết
        /// </summary>
        public string DetailTable { get; set; }

        /// <summary>
        /// Thủ tục để thêm mới vào bảng chính
        /// </summary>
        public string MasterInsert { get; set; }
        /// <summary>
        /// Thủ tục để cập nhật vào bảng chính
        /// </summary>
        public string MasterUpdate { get; set; }
        /// <summary>
        /// Thủ tục để xóa một bản ghi khỏi bảng chính
        /// </summary>
        public string MasterDelete { get; set; }

        /// <summary>
        /// Thủ tục thêm một bản ghi vào bảng chi tiết
        /// </summary>
        public string DetailInsert { get; set; }
        /// <summary>
        /// Thủ tục cập nhật một bản ghi vào bảng chi tiết
        /// </summary>
        public string DetailUpdate { get; set; }
        /// <summary>
        /// Thủ tục xóa một bản ghi khỏi khỏi bảng chi tiết
        /// </summary>
        public string DetailDelete { get; set; }

        /// <summary>
        /// Ánh xạ tên cột khóa chính của bảng chi tiết sang bảng cha (ColumnMaster,ColumnDetail)
        /// </summary>
        public Dictionary<string, string> PrimaryKeyMaps { get; set; }

        public string RelationName { get; set; }

        public virtual void Validate(ValidateMode mode, DataRow masterRow, DataRow[] detailRows)
        {
        }

        public virtual void Insert(SqlTransaction transaction, DataRow masterRow, DataRow[] detailRows)
        {
            bool internalTransaction = false;
            if (detailRows == null)
            {
                detailRows = masterRow.GetChildRows(RelationName);
                if (detailRows == null)
                    throw new Exception("no found child rows with master row");
            }
            Validate(ValidateMode.Insert, masterRow, detailRows);


            if (transaction == null)
            {
                transaction = CreateInternalTransaction();
                internalTransaction = true;
            }
            try
            {
                List<string> columnChanged = new List<string>();
                using (DataTable tblTemp = masterRow.Table.Clone())
                {
                    //Lưu row ban đầu để so sánh xem có sự khác nhau không
                    tblTemp.ImportRow(masterRow);
                    SqlHelper.ExecuteNonQuery(transaction, MasterInsert, masterRow);
                    //Check xem khóa chính có bị thay đổi không
                    string[] listColumn = PrimaryKeyMaps.Keys.ToArray<string>();
                    for (int i = 0; i < listColumn.Length; i++)
                    {
                        if (tblTemp.Rows[0][listColumn[i]] != masterRow[listColumn[i]])
                            columnChanged.Add(listColumn[i]);
                    }
                }

                for (int i = 0; i < detailRows.Length; i++)
                {
                    DataRow currentDetail = detailRows[i];
                    string[] allKey = columnChanged.ToArray();
                    for (int j = 0; j < allKey.Length; j++)
                    {
                        currentDetail[PrimaryKeyMaps[allKey[j]]] = masterRow[allKey[j]];
                    }

                    SqlHelper.ExecuteNonQuery(transaction, DetailInsert, currentDetail);
                }
                if (internalTransaction)
                    transaction.Commit();

            }
            catch (Exception ex)
            {
                if (internalTransaction)
                    transaction.Rollback();
                throw ex;
            }
            finally
            {
                if (internalTransaction)
                {
                    if (transaction.Connection.State != ConnectionState.Closed)
                        transaction.Connection.Close();
                    transaction.Connection.Dispose();
                    transaction.Dispose();

                }
            }
        }

        public virtual void Update(SqlTransaction transaction, DataRow masterRow, DataRow[] detailRows)
        {
            bool internalTransaction = false;
            if (detailRows == null)
            {
                detailRows = masterRow.GetChildRows(RelationName);
                if (detailRows == null)
                    throw new Exception("no found child rows with master row");
            }
            Validate(ValidateMode.Update, masterRow, detailRows);


            if (transaction == null)
            {
                transaction = CreateInternalTransaction();
                internalTransaction = true;
            }
            try
            {
                //Doạn check này có thể bỏ nếu mà Relation Đặt Cascade và giàn buộc ở cả master and detail
                List<string> columnChanged = new List<string>();
                using (DataTable tblTemp = masterRow.Table.Clone())
                {
                    //Lưu row ban đầu để so sánh xem có sự khác nhau không
                    tblTemp.ImportRow(masterRow);
                    SqlHelper.ExecuteNonQuery(transaction, MasterUpdate, masterRow);
                    //Check xem khóa chính có bị thay đổi không
                    string[] listColumn = PrimaryKeyMaps.Keys.ToArray<string>();
                    for (int i = 0; i < listColumn.Length; i++)
                    {
                        if (tblTemp.Rows[0][listColumn[i]] != masterRow[listColumn[i]])
                            columnChanged.Add(listColumn[i]);
                    }
                }

                for (int i = 0; i < detailRows.Length; i++)
                {
                    DataRow currentDetail = detailRows[i];
                    //Doạn check này có thể bỏ nếu mà Relation Đặt Cascade và giàn buộc ở cả master and detail
                    string[] allKey = columnChanged.ToArray();
                    for (int j = 0; j < allKey.Length; j++)
                    {
                        currentDetail[PrimaryKeyMaps[allKey[j]]] = masterRow[allKey[j]];
                    }

                    SqlHelper.ExecuteNonQuery(transaction, DetailUpdate, currentDetail);
                }
                if (internalTransaction)
                    transaction.Commit();

            }
            catch (Exception ex)
            {
                if (internalTransaction)
                    transaction.Rollback();
                throw ex;
            }
            finally
            {
                if (internalTransaction)
                {
                    if (transaction.Connection.State != ConnectionState.Closed)
                        transaction.Connection.Close();
                    transaction.Connection.Dispose();
                    transaction.Dispose();

                }
            }
        }

        public virtual void Delete(SqlTransaction transaction, DataRow masterRow, DataRow[] detailRows)
        {
            bool internalTransaction = false;
            if (detailRows == null)
            {
                detailRows = masterRow.GetChildRows(RelationName);
                if (detailRows == null)
                    throw new Exception("no found child rows with master row");
            }
            Validate(ValidateMode.Delete, masterRow, detailRows);
            if (transaction == null)
            {
                transaction = CreateInternalTransaction();
                internalTransaction = true;
            }
            if (transaction == null)
            {
                transaction = CreateInternalTransaction();
                internalTransaction = true;
            }
            try
            {


                SqlHelper.ExecuteNonQuery(transaction, MasterDelete, masterRow);

                for (int i = 0; i < detailRows.Length; i++)
                {
                    DataRow currentDetail = detailRows[i];
                    SqlHelper.ExecuteNonQuery(transaction, DetailDelete, currentDetail);
                }
                if (internalTransaction)
                    transaction.Commit();

            }
            catch (Exception ex)
            {
                if (internalTransaction)
                    transaction.Rollback();
                throw ex;
            }
            finally
            {
                if (internalTransaction)
                {
                    if (transaction.Connection.State != ConnectionState.Closed)
                        transaction.Connection.Close();
                    transaction.Connection.Dispose();
                    transaction.Dispose();
                }
            }
        }
        public virtual void Fill(DataSet ds, string masterWhere, string[] masterField, string[] detailField, bool clearBeforeFill)
        {
            if (clearBeforeFill)
            {
                ds.Clear();
            }

            SqlConnection connection = new SqlConnection(KP_OCnnSql.Cnn_String);
            SqlDataAdapter adapter = null;
            string[] sql = BuildSqlSelect(masterWhere, masterField, detailField);
            ds.EnforceConstraints = false;
            try
            {
                connection.Open();
                adapter = new SqlDataAdapter(sql[0], connection);
                adapter.Fill(ds, MasterTable);

                adapter.SelectCommand.CommandText = sql[1];
                adapter.Fill(ds, DetailTable);

                bool existRelation = ds.Relations.Contains(RelationName);
                bool existConstraint = ds.Tables[DetailTable].Constraints.Contains("FK_" + RelationName);

                //Make Relation - ForeignKeyConstraint
                string[] listColumn = PrimaryKeyMaps.Keys.ToArray<string>();
                List<DataColumn> lstParent = new List<DataColumn>();
                List<DataColumn> lstChild = new List<DataColumn>();
                foreach (string node in listColumn)
                {
                    lstParent.Add(ds.Tables[MasterTable].Columns[node]);
                    lstChild.Add(ds.Tables[DetailTable].Columns[PrimaryKeyMaps[node]]);
                }

                if (!existConstraint)
                {
                    ForeignKeyConstraint fkc = new ForeignKeyConstraint("FK_" + RelationName, lstParent.ToArray(), lstChild.ToArray());
                    fkc.AcceptRejectRule = AcceptRejectRule.Cascade;
                    fkc.DeleteRule = Rule.Cascade;
                    fkc.UpdateRule = Rule.Cascade;

                    ds.Tables[DetailTable].Constraints.Add(fkc);
                }

                if (!existRelation)
                {
                    DataRelation dr = new DataRelation(RelationName, lstParent.ToArray(), lstChild.ToArray(), false);
                    ds.Relations.Add(dr);
                }
                ds.EnforceConstraints = true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (adapter != null)
                    adapter.Dispose();
                if (connection.State != ConnectionState.Closed)
                    connection.Close();
                connection.Dispose();
            }
        }
        public virtual DataSet Select(string masterWhere, string[] masterField, string[] detailField)
        {
            SqlConnection connection = new SqlConnection(KP_OCnnSql.Cnn_String);
            SqlDataAdapter adapter = null;
            string[] sql = BuildSqlSelect(masterWhere, masterField, detailField);
            DataSet ds = new DataSet(MasterTable + "_" + DetailTable);
            try
            {
                connection.Open();
                adapter = new SqlDataAdapter(sql[0], connection);
                adapter.Fill(ds, MasterTable);

                adapter.SelectCommand.CommandText = sql[1];
                adapter.Fill(ds, DetailTable);

                //Make Relation - ForeignKeyConstraint
                string[] listColumn = PrimaryKeyMaps.Keys.ToArray<string>();
                List<DataColumn> lstParent = new List<DataColumn>();
                List<DataColumn> lstChild = new List<DataColumn>();
                foreach (string node in listColumn)
                {
                    lstParent.Add(ds.Tables[MasterTable].Columns[node]);
                    lstChild.Add(ds.Tables[DetailTable].Columns[PrimaryKeyMaps[node]]);
                }

                ForeignKeyConstraint fkc = new ForeignKeyConstraint(RelationName, lstParent.ToArray(), lstChild.ToArray());
                fkc.AcceptRejectRule = AcceptRejectRule.Cascade;
                fkc.DeleteRule = Rule.Cascade;
                fkc.UpdateRule = Rule.Cascade;

                ds.Tables[DetailTable].Constraints.Add(fkc);

                DataRelation dr = new DataRelation(RelationName, lstParent.ToArray(), lstChild.ToArray(), false);
                ds.Relations.Add(dr);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (adapter != null)
                    adapter.Dispose();
                if (connection.State != ConnectionState.Closed)
                    connection.Close();
                connection.Dispose();
            }
            return ds;
        }

        private string[] BuildSqlSelect(string masterWhere, string[] masterField, string[] detailField)
        {
            string[] arr = new string[2];

            if (masterField != null || masterField.Length > 0)
            {
                if (masterField.Length > 1)
                    arr[0] = string.Join(", ", masterField);
                else
                    arr[0] = masterField[0];
            }
            else
                arr[0] = "*";

            arr[0] = "Select " + arr[0] + " From " + MasterTable;

            if (!string.IsNullOrEmpty(masterWhere))
            {
                arr[0] += " Where " + masterWhere;
            }

            if (detailField != null && detailField.Length > 0)
            {
                if (detailField.Length > 1)
                    arr[1] = string.Join(", tbl1.", detailField);
                else
                    arr[1] = detailField[0];
            }
            else
                arr[1] = "*";
            arr[1] = "tbl1." + arr[1];

            arr[1] = "Select " + arr[1] + " From " + DetailTable;
            arr[1] += " tbl1";

            if (!string.IsNullOrEmpty(masterWhere))
            {
                //Alias Detail

                arr[1] += " INNER JOIN (" + arr[0] + ") tbl2 ON ";
                string[] listColumn = PrimaryKeyMaps.Keys.ToArray<string>();
                string joinCondition = "";
                for (int i = 0; i < listColumn.Length; i++)
                {
                    joinCondition += " tbl1." + PrimaryKeyMaps[listColumn[i]] + " = " + "tbl2." + listColumn[i] + " AND";
                }
                if (joinCondition.EndsWith("AND"))
                    joinCondition = joinCondition.Substring(0, joinCondition.Length - 4);

                arr[1] += joinCondition;
            }


            return arr;
        }

        private SqlTransaction CreateInternalTransaction()
        {
            SqlConnection connection = new SqlConnection(KP_OCnnSql.Cnn_String);
            connection.Open();
            return connection.BeginTransaction();

        }


        #region Constructor
        public KP_DALVoucher2()
        {
        }
        public KP_DALVoucher2(string masterTable, string detailTable, bool autoProc)
        {
            MasterTable = masterTable;
            DetailTable = detailTable;
            if (autoProc)
            {
                MasterInsert = DALHelper.GetInsertProc(masterTable);
                MasterUpdate = DALHelper.GetUpdateProc(masterTable);
                MasterDelete = DALHelper.GetDeleteProc(masterTable);

                DetailInsert = DALHelper.GetInsertProc(detailTable);
                DetailDelete = DALHelper.GetDeleteProc(detailTable);
                DetailUpdate = DALHelper.GetUpdateProc(detailTable);

            }
            RelationName = MasterTable + "_" + DetailTable;
        }

        public KP_DALVoucher2(string masterTable, string masterInsert, string masterUpdate, string masterDelete, string detailTable, string detailInsert, string detailUpdate, string detailDelete, string relationName)
            : this(masterTable, detailTable, false)
        {
            MasterInsert = masterInsert;
            MasterUpdate = masterUpdate;
            MasterDelete = masterDelete;

            DetailInsert = detailInsert;
            DetailDelete = detailDelete;
            DetailUpdate = detailUpdate;

            if (!string.IsNullOrEmpty(relationName))
                RelationName = relationName;
        }
        #endregion

    }
}
